CREATE DEFINER=`computraceqa`@`%` PROCEDURE `computraceqa`.`GetSystemSleepDetails`(
    IN p_emp_code VARCHAR(255),
    IN p_triggered_on TIMESTAMP,
    IN p_triggered_off TIMESTAMP,
    IN p_description VARCHAR(50),
    IN p_page INT,
    IN p_page_size INT,
    IN p_ignore_paging BOOLEAN,
    IN p_sort_column VARCHAR(50),  -- Added parameter for sort column
    IN p_sort_type VARCHAR(4)      -- Added parameter for sort type ('ASC' or 'DESC')
)
BEGIN
    DECLARE v_offset INT;
    DECLARE v_total_records INT;
    DECLARE v_total_pages INT;
    DECLARE v_sort_query VARCHAR(255);
    DECLARE v_limit_query VARCHAR(100);
    DECLARE v_actual_sort_column VARCHAR(50);

    -- Map the model class property names to the actual table column names
    SET v_actual_sort_column = CASE p_sort_column
        WHEN 'Id' THEN 'id'
        WHEN 'EmpCode' THEN 'emp_code'
        WHEN 'Description' THEN 'description'
        WHEN 'TriggeredOn' THEN 'triggered_on'
        WHEN 'CreatedOn' THEN 'created_on'
        WHEN 'UpdatedOn' THEN 'updated_on'
        ELSE 'triggered_on'  -- Default column if no match is found
    END;

    -- Set default sort type if not provided
    IF p_sort_type IS NULL OR (p_sort_type != 'ASC' AND p_sort_type != 'DESC') THEN
        SET p_sort_type = 'DESC';
    END IF;

    -- Build the ORDER BY clause dynamically
    SET v_sort_query = CONCAT(' ORDER BY ', v_actual_sort_column, ' ', p_sort_type);

    -- Calculate the offset for pagination if paging is not ignored
    IF NOT p_ignore_paging THEN
        SET v_offset = (p_page - 1) * p_page_size;
        SET v_limit_query = CONCAT(' LIMIT ', v_offset, ', ', p_page_size);
    ELSE
        SET v_limit_query = ''; -- No LIMIT clause if paging is ignored
    END IF;

    -- Calculate the total number of records
    SET @query = CONCAT(
        'SELECT COUNT(*) INTO @v_total_records FROM system_sleep_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_triggered_on IS NOT NULL, CONCAT('AND DATE(triggered_on) >= DATE("', p_triggered_on, '") '), ''),
        IF(p_triggered_off IS NOT NULL, CONCAT('AND DATE(triggered_on) <= DATE("', p_triggered_off, '") '), ''),
        IF(p_description IS NOT NULL AND p_description != '', CONCAT('AND lower(description) LIKE "%', p_description, '%" '), '')
    );
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    SELECT @v_total_records INTO v_total_records;
    SET v_total_pages = CEIL(v_total_records / p_page_size);

    -- Select the filtered and paginated results along with total records and total pages
    SET @query = CONCAT(
        'SELECT id, emp_code, description, triggered_on, created_on, updated_on, ', 
        v_total_records, ' AS total_records, ', 
        v_total_pages, ' AS total_pages ',
        'FROM system_sleep_details WHERE 1 = 1 ',
        IF(p_emp_code IS NOT NULL AND p_emp_code != '', CONCAT('AND lower(emp_code) LIKE "%', p_emp_code, '%" '), ''),
        IF(p_triggered_on IS NOT NULL, CONCAT('AND DATE(triggered_on) >= DATE("', p_triggered_on, '") '), ''),
        IF(p_triggered_off IS NOT NULL, CONCAT('AND DATE(triggered_on) <= DATE("', p_triggered_off, '") '), ''),
        IF(p_description IS NOT NULL AND p_description != '', CONCAT('AND lower(description) LIKE "%', p_description, '%" '), ''),
        v_sort_query,
        v_limit_query
    );

    -- Execute the dynamically built query
    PREPARE stmt FROM @query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END